<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

abstract class Entidade {

    private $bd;
    private $nomeEnt = null;
    private $camposEnt = array();

    function __construct() {
        $this->bd = TConnection::open('my_evento');
        $this->nomeEnt = strtolower(get_class($this));
        $this->camposEnt = array_diff(array_keys(get_class_vars($this->nomeEnt)), array_keys(get_class_vars(__CLASS__)));
    }

    function find($id, $campos = array()) {
        if (!empty($campos)) {
            $campos = implode(', ', $campos);
        } else {
            $campos = implode(', ', $this->camposEnt);
        }

        $resultado = & new $this->nomeEnt();
        if (!is_null($id) && $id != '') {
            $query = "select $campos from $this->nomeEnt where id = $id";

            try {
                $resultado = $this->bd->query($query);

                if (!$resultado) {
                    $resultado = & new $this->nomeEnt();
                } else {
                    $resultado = $resultado->fetchObject($this->nomeEnt);
                }
            } catch (PDOException $exc) {
                echo $exc->getMessage();
            }
        }
        return $resultado;
    }

    public function find2($options = array()) {
        $criterio = "";
        $campos = "";
        $camposjoin = "";
        $join = "";
        $group = "";
        $limit = "";
        $order = "";
        if (is_array($options)) {
            foreach ($options as $key => $value) {
                if ($key === "conditions") {
                    $criterio = $this->criterio($value);
                }
                if ($key === "fields") {
                    $campos = is_array($value) ? implode(', ', $value) : $value;
                }

                if ($key === 'limit') {
                    $limit = " limit " . $this->transform($value);
                }

                if (stripos($key, 'order') > -1) {
                    $order = " order by ";
                    $order .= is_array($value) ? implode(', ', $value) : $value;
                }

                if ($key == 'join') {
                    $join = $this->join($value);
                    foreach ($value as $table) {
                        $camposjoin .= "$table.*, ";
                    }
                    $camposjoin = ", $camposjoin";
                    $camposjoin = substr($camposjoin, 0, -2);
                }

                if ($key == 'group') {
                    $group = " group by ";
                    $group .= is_array($value) ? implode(', ', $value) : $value;
                }
            }
        }
        if ($campos == "") {
            $campos = implode(', ', $this->camposEnt);
        } else {
            $camposjoin = "";
        }

        $resultado = & new $this->nomeEnt();
        $query = "select $campos $camposjoin from $this->nomeEnt $join $criterio $group $order $limit";
        try {
            $resultado = $this->bd->query($query);
        } catch (PDOException $exc) {
            echo $exc->getMessage();
        }

        return $resultado;
    }

    private function criterio($param) {
        $criterio = "";
        if (is_array($param)) {
            foreach ($param as $key => $value) {
                if (!is_array($value)) {
                    $campo = trim($key);
                    //$campo = strpos($campo, "=") === FALSE ? $campo."=" : $campo; 
                    $criterio .= "$campo " . $this->transform($value) . " and ";
                }
            }
        }
        if ($criterio !== "") {
            $criterio = substr($criterio, 0, -5);
            $criterio = " where " . $criterio;
        }
        return $criterio;
    }

    private function join($tables) {
        $join = "";
        if (is_array($tables)) {
            foreach ($tables as $value) {
                $join = " inner join $value on $value.id = {$value}_id ";
            }
        }
        return $join;
    }

    public function save(& $data, & $error = null) {
        $resultado = true;
        if (!is_null($data)) {
            $campos = $values = "";
            $intersecao = array_intersect($this->camposEnt, array_keys($data));
            if (!isset($data['id']) || $data['id'] === '') {
                foreach ($intersecao as $key) {
                    $campos .= "{$key}, ";
                    $values .= $this->transform($data[$key]) . ", ";
                }
                $campos = substr($campos, 0, -2);
                $values = substr($values, 0, -2);
                try {
                    $stmt = "insert into {$this->nomeEnt} ({$campos}) values ({$values})";
                    $this->bd->exec($stmt);
                    $data['id'] = $this->bd->lastInsertId();
                } catch (PDOException $exc) {
                    echo $exc->getMessage();
                    $resultado = false;
                }
            } else {
                $set = "";
                foreach ($intersecao as $key) {
                    $set .= "{$key} = " . $this->transform($data[$key]) . ", ";
                }
                $set = substr($set, 0, -2);
                try {
                    $stmt = "update {$this->nomeEnt} set {$set} where id = {$data['id']}";
                    $this->bd->exec($stmt);
                } catch (PDOException $exc) {
                    echo $exc->getMessage();

                    $resultado = false;
                }
            }

            $error = $this->execute('show_error');
        }

        return $resultado;
    }

    private function saves(& $data, & $error, $entidade = array('camposEnt' => array(),
        'nomeEnt' => NULL)) {
        $result = true;
        $camposEnt = $this->camposEnt;
        $nomeEnt = $this->nomeEnt;

        $this->camposEnt = $entidade['camposEnt'];
        $this->nomeEnt = $entidade['nomeEnt'];

        $result = $this->save($data, $error);

        $this->camposEnt = $camposEnt;
        $this->nomeEnt = $nomeEnt;

        return $result;
    }

    function findAll($campos = array()) {
        if (!empty($campos)) {
            $campos = implode(', ', $campos);
        } else {
            $campos = implode(', ', $this->camposEnt);
        }

        $query = "select $campos from $this->nomeEnt";
        $resultado = NULL;

        try {
            $resultado = $this->bd->query($query);
        } catch (PDOException $exc) {
            echo $exc->getMessage();
            return false;
        }

        return $resultado;
    }

    function delete($params) {
        if (is_array($params)) {
            $criterio = $this->criterio($params);
        } else {
            $criterio = " where id = '$params'";
        }

        $query = "delete from $this->nomeEnt $criterio";

        $resultado = true;
        try {
            $this->bd->exec($query);
        } catch (PDOException $exc) {
            echo $exc->getMessage();
            return false;
        }

        return $resultado;
    }

    private function transform($value) {
        if (is_array($value)) {
            foreach ($array as $x) {
                if (is_integer($x)) {
                    $foo[] = $x;
                } elseif (is_string($x)) {
                    $foo[] = "'$x'";
                }
            }
            $result = '(' . implode(',', $foo) . ')';
        } elseif (is_string($value)) {
            $result = "'$value'";
        } elseif (is_null($value)) {
            $result = 'NULL';
        } elseif (is_bool($value)) {
            $result = $value ? 'TRUE' : 'FALSE';
        } else {
            $result = $value;
        }

        return $result;
    }

    function criarUserBd($usuario, $senha) {
        $query = "GRANT USAGE ON *.* TO $usuario@localhost IDENTIFIED BY '$senha'";
        try {
            $resultado = $this->bd->query($query);
        } catch (PDOException $exc) {
            echo $exc->getMessage();
        }

        //return $resultado;
    }

    function deletarSGBD($usuario) {
        $query = "DROP USER $usuario@localhost";
        try {
            $resultado = $this->bd->query($query);
            return 1;
        } catch (PDOException $exc) {
            echo $exc->getMessage();
            return 0;
        }
    }

    function criarPermissoes($email, $grupo) {
        $permissao = "";

        if ($grupo == "0") {
            $query = "GRANT EXECUTE ON PROCEDURE nailtonv_projeto.sp_inscricao TO $email@localhost";
            $resultado = $this->bd->query($query);
            ////////////////////////////////////////////////////////////////////////////////
            $query = "GRANT EXECUTE ON FUNCTION nailtonv_projeto.strSplit TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT EXECUTE ON FUNCTION nailtonv_projeto.dataFinalEvento TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT EXECUTE ON FUNCTION nailtonv_projeto.numCobrancas_evento TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT EXECUTE ON FUNCTION nailtonv_projeto.numparticipantes TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT EXECUTE ON FUNCTION nailtonv_projeto.precoEvento_Cobranca TO $email@localhost";
            $resultado = $this->bd->query($query);
            /////////////////////////////////////////////////////////////////////////////////////////////
            $query = "GRANT SELECT,INSERT ON nailtonv_projeto.inscricao TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT ON nailtonv_projeto.atividade TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT ON nailtonv_projeto.precoevento TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT ON nailtonv_projeto.evento TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT ON nailtonv_projeto.atividade_has_inscricao TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT ON nailtonv_projeto.cobranca TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT ON nailtonv_projeto.usuario TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT ON nailtonv_projeto.cidade TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT ON nailtonv_projeto.estado TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT ON nailtonv_projeto.view_evatcatpreco TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT ON nailtonv_projeto.view_usucidcatgruins TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT ON nailtonv_projeto.vw_eventos_abertos TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT ON nailtonv_projeto.view_preco TO $email@localhost";
            $resultado = $this->bd->query($query);
        } else if ($grupo == "1") {
            $query = "GRANT EXECUTE ON PROCEDURE nailtonv_projeto.sp_inscricao TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT EXECUTE ON PROCEDURE nailtonv_projeto.relatorio_perc_promocoes_evento TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT EXECUTE ON PROCEDURE nailtonv_projeto.show_error TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT EXECUTE ON PROCEDURE nailtonv_projeto.sp_precoEvento TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT EXECUTE ON PROCEDURE nailtonv_projeto.sp_precoEventoListar TO $email@localhost";
            $resultado = $this->bd->query($query);
            ///////////////////////////////////////////////////////////////////////////////////////////////
            $query = "GRANT EXECUTE ON FUNCTION nailtonv_projeto.strSplit TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT EXECUTE ON FUNCTION nailtonv_projeto.dataFinalEvento TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT EXECUTE ON FUNCTION nailtonv_projeto.numCobrancas_evento TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT EXECUTE ON FUNCTION nailtonv_projeto.numparticipantes TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT EXECUTE ON FUNCTION nailtonv_projeto.precoEvento_Cobranca TO $email@localhost";
            $resultado = $this->bd->query($query);
            ///////////////////////////////////////////////////////////////////////////////////////////////
            $query = "GRANT SELECT,INSERT,UPDATE,DELETE ON nailtonv_projeto.inscricao TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT,UPDATE,DELETE ON nailtonv_projeto.atividade_has_inscricao TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT,UPDATE,DELETE ON nailtonv_projeto.cobranca TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT,UPDATE,DELETE ON nailtonv_projeto.usuario TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT,UPDATE ON nailtonv_projeto.evento TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT,UPDATE,DELETE ON nailtonv_projeto.atividade TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT ON nailtonv_projeto.tipo TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT,UPDATE,DELETE ON nailtonv_projeto.precoatividade TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT,UPDATE,DELETE ON nailtonv_projeto.precoevento TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT ON nailtonv_projeto.cidade TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT ON nailtonv_projeto.estado TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT ON nailtonv_projeto.view_evatcatpreco TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT ON nailtonv_projeto.view_usucidcatgruins TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT ON nailtonv_projeto.vw_eventos_abertos TO $email@localhost";
            $resultado = $this->bd->query($query);
            $query = "GRANT SELECT,INSERT ON nailtonv_projeto.view_preco TO $email@localhost";
            $resultado = $this->bd->query($query);
        } else {
            $query = "GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON nailtonv_projeto.* TO $email@localhost";
            $resultado = $this->bd->query($query);
        }

//        try {
//            $resultado = $this->bd->query($query);
//        } catch (PDOException $exc) {
//            echo $exc->getMessage();
//        }
    }

    function usarUserBd($usuario, $senha, $name, $host, $port) {

        $_SESSION['usuario'] = $usuario;
        $_SESSION['senha'] = $senha;
        $_SESSION['name'] = $name;
        $_SESSION['host'] = $host;

//        try {
//            $conn = new PDO("mysql:host={$host};port={$port};dbname={$name}", $usuario, $senha,
//                                    array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'utf8\''));
//
//        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//        print_r($conn);
//        exit();
//        return $conn;
//        
//        } catch (PDOException $exc) {
//            echo $exc->getMessage();
//            return 0;
//        }
        //return $resultado;
    }

    function execute($procedure, $params = null) {
        $par = "(";
        $parArray = array();
        if (is_array($params)) {
            foreach ($params as $key => $value) {
                $par .= '?,';
                $parArray[$key] = $value;
            }
            $par = substr($par, 0, -1);
        } else if (!is_null($params)) {
            $par .= "?";
            $parArray[0] = $params;
        }

        $par .= ")";
        $stmt = $this->bd->prepare("call $procedure$par;");

        $resultado = NULL;

        try {
            $resultado = $stmt->execute($parArray);
            if ($resultado == 1) {
                $resultado = $stmt->fetchAll(PDO::FETCH_ASSOC);
            }
        } catch (PDOException $exc) {
            echo $exc->getMessage();
            return false;
        }

        return $resultado;
    }

    function saveAll($data, & $error = null, $table = null) {
        if (empty($data)) {
            return true;
        }
        $cont = 0;
        $result = false;
        $nomeEnt = is_null($table) ? $this->nomeEnt : $table;

        if (is_null($table)) {
            $this->bd->beginTransaction();
        }

        $model = isset($data[$nomeEnt]) ? $data[$nomeEnt] : $data;

        $numero = implode(array_keys($model));
        if (is_numeric($numero)) {
            foreach ($model as $value) {
                $result = $this->saveAll($value, $error, $nomeEnt);
                if (!$result) {
                    break;
                }
            }
        } else {
            $nomeEnt = strtoupper($nomeEnt[0]) . substr($nomeEnt, 1);
            include_once $nomeEnt . ".php";

            $camposEnt = array_diff(array_keys(get_class_vars($nomeEnt)), array_keys(get_class_vars(__CLASS__)));

            $sub = array_diff(array_keys($data), $camposEnt);

            $result = $this->saves($data, $error, array('camposEnt' => $camposEnt,
                'nomeEnt' => strtolower($nomeEnt)));

            if ($result) {
                foreach ($sub as $value) {
                    $numero = null;
                    if (is_array($data[$value])) {
                        $numero = implode(array_keys($data[$value]));
                    }

                    if (is_numeric($numero)) {
                        foreach ($data[$value] as $value2) {
                            $value2[strtolower($nomeEnt) . "_id"] = $data['id'];
                            $result = $this->saveAll($value2, $error, $value);
                            if (!$result) {
                                break;
                            }
                        }
                    } else {
//                        if (!empty ($data[$value])){
//                            $data[$value][strtolower($nomeEnt)."_id"] = $data['id'];
//                            $result = $this->saveAll($data[$value], $error, $value);
//                        }
                    }
                    if (!$result) {
                        break;
                    }
                }
            }
        }

        if (is_null($table)) {
            if ($result) {
                $this->bd->commit();
            } else {
                $this->bd->rollBack();
            }
        }

        return $result;
    }

}

?>
